﻿using System;
using System.Linq;
using System.Collections.Generic;
using System.Data.SqlClient;
using T4Scaffolding.Cmdlets;
using System.Data.Entity.Design.PluralizationServices;
using System.Globalization;

namespace Caramel.Entities.Scaffolding.Metadata.SqlServer
{
	public class SqlServerCatalogMetadataProvider
	{
		public SqlServerCatalogMetadataProvider()
		{
			this.DataSource = ".";
			this.InitialCatalog = "Accumed20";
			this.IncludedSchemas = new[] { "dbo" };
			this.PluralizationService = PluralizationService.CreateService(CultureInfo.CurrentCulture);
		}

		public string DataSource { get; set; }

		public string InitialCatalog { get; set; }

		public IEnumerable<string> IncludedSchemas { get; set; }

		public IEnumerable<string> IncludedTables { get; set; }

		public IEnumerable<string> ExcludedTables { get; set; }

		public PluralizationService PluralizationService { get; set; }

		public Catalog GetCatalogMetadata()
		{
			var builder =
				new SqlConnectionStringBuilder()
				{
					DataSource = this.DataSource,
					InitialCatalog = this.InitialCatalog,
					IntegratedSecurity = true,
					MultipleActiveResultSets = true
				};

			var catalog = new Catalog() { Name = this.InitialCatalog };

			using (var connection = new SqlConnection(builder.ConnectionString))
			{
				connection.Open();

				var command =
					new SqlCommand()
					{
						Connection = connection
					};

				command.CommandText = @"
						SELECT 
							T.TABLE_NAME, T.TABLE_SCHEMA
						FROM
							INFORMATION_SCHEMA.TABLES T
						WHERE
							T.TABLE_TYPE = 'BASE TABLE'
							AND 
							T.TABLE_NAME NOT LIKE 'aspnet_%'
							AND
							T.TABLE_NAME NOT LIKE 'sys%'";

				if ((this.IncludedTables != null) && this.IncludedTables.Any())
				{
					foreach (var includedTable in this.IncludedTables)
					{
						command.CommandText += @"
							AND
							T.TABLE_NAME = '" + includedTable + "'";
					}
				}

				if ((this.ExcludedTables != null) && this.ExcludedTables.Any())
				{
					foreach (var includedTable in this.ExcludedTables)
					{
						command.CommandText += @"
							AND
							T.TABLE_NAME <> '" + includedTable + "'";
					}
				}

				command.CommandText += @"
						ORDER BY 
							T.TABLE_NAME";

				using (var reader = command.ExecuteReader())
				{
					while (reader.Read())
					{
						var schema = catalog.Schemas.FirstOrDefault(s => s.Name == reader.GetString(reader.GetOrdinal("TABLE_SCHEMA")));

						if (schema == null)
						{
							schema =
								new Schema()
								{
									Name = reader.GetString(reader.GetOrdinal("TABLE_SCHEMA"))
								};

							schema.Catalog = catalog;
							catalog.Schemas.Add(schema);
						}

						var table = new Table();
						table.Schema = schema;
						table.Name = reader.GetString(reader.GetOrdinal("TABLE_NAME"));
						table.SingularizedName = this.PluralizationService.Singularize(table.Name);
						table.PluralizedName = this.PluralizationService.Pluralize(table.Name);

						schema.Tables.Add(table);
					}
				}

				foreach (var table in catalog.Schemas.SelectMany(s => s.Tables))
				{
					this.GetColumns(table, connection);
				}

				foreach (var schema in catalog.Schemas)
				{
					this.GetRelations(schema, connection);
				}
			}

			return catalog;
		}

		private void GetRelations(Schema schema, SqlConnection connection)
		{
			var command =
				new SqlCommand(
					String.
						Format(@"			
							SELECT
								CCU.[TABLE_NAME] [ChildTable],
								CCU.[COLUMN_NAME] [ChildColumn],
								TC2.[TABLE_NAME] [ParentTable],
								RC.[CONSTRAINT_NAME] [Name]
							FROM 
								INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
									INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC ON
										RC.CONSTRAINT_CATALOG = TC.CONSTRAINT_CATALOG
										AND		
										RC.CONSTRAINT_SCHEMA = TC.CONSTRAINT_SCHEMA
										AND
										RC.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
									INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU ON 
										TC.CONSTRAINT_CATALOG = CCU.CONSTRAINT_CATALOG
										AND		
										TC.CONSTRAINT_SCHEMA = CCU.CONSTRAINT_SCHEMA
										AND
										TC.CONSTRAINT_NAME = CCU.CONSTRAINT_NAME
									INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC2 ON
										RC.UNIQUE_CONSTRAINT_CATALOG = TC2.CONSTRAINT_CATALOG
										AND		
										RC.UNIQUE_CONSTRAINT_SCHEMA = TC2.CONSTRAINT_SCHEMA
										AND
										RC.UNIQUE_CONSTRAINT_NAME = TC2.CONSTRAINT_NAME		
							WHERE 
								CCU.TABLE_NAME NOT LIKE 'aspnet_%'
								AND
								CCU.TABLE_NAME NOT LIKE 'sys%'
								AND
								RC.CONSTRAINT_CATALOG = '{0}'
								AND
								RC.CONSTRAINT_SCHEMA = '{1}'
								AND
								TC.CONSTRAINT_TYPE = 'FOREIGN KEY'
							ORDER BY
								CCU.[TABLE_NAME],
								TC2.[TABLE_NAME],
								CCU.[COLUMN_NAME]",
							schema.Catalog,
							schema
						),
					connection);

			using (var reader = command.ExecuteReader())
			{
				while (reader.Read())
				{
					var relation = new Relation();
					relation.Name = reader.GetString(reader.GetOrdinal("Name"));
					relation.Parent = schema.Tables.First(t => t.Name.Equals(reader.GetString(reader.GetOrdinal("ParentTable")), StringComparison.InvariantCultureIgnoreCase));
					relation.Child = schema.Tables.First(t => t.Name.Equals(reader.GetString(reader.GetOrdinal("ChildTable")), StringComparison.InvariantCultureIgnoreCase));
					relation.Parent.Relations.Add(relation);
					relation.Child.Relations.Add(relation);

					var childColumn = reader.GetString(reader.GetOrdinal("ChildColumn"));

					if (relation.Child.PrimaryKeys.Any(c => c.Name.Equals(childColumn, StringComparison.InvariantCultureIgnoreCase)))
					{
						relation.ChildColumns.Add(relation.Child.PrimaryKeys.Single(c => c.Name.Equals(childColumn, StringComparison.InvariantCultureIgnoreCase)));
					}

					if (relation.Child.Columns.Any(c => c.Name.Equals(childColumn, StringComparison.InvariantCultureIgnoreCase)))
					{
						relation.ChildColumns.Add(relation.Child.Columns.Single(c => c.Name.Equals(childColumn, StringComparison.InvariantCultureIgnoreCase)));
						relation.Child.Columns.Remove(relation.Child.Columns.First(c => c.Name.Equals(childColumn, StringComparison.InvariantCultureIgnoreCase)));
					}
				}
			}
		}

		private void GetColumns(Table table, SqlConnection connection)
		{
			var command =
				new SqlCommand(
					String.
						Format(@"
							SELECT
								C.COLUMN_NAME, 
								C.DATA_TYPE, 
								CASE C.IS_NULLABLE
									WHEN 'NO' THEN CAST(0 AS BIT)
									ELSE CAST(1 AS BIT)
								END [IS_NULLABLE],
								C.CHARACTER_MAXIMUM_LENGTH,
								(
									SELECT
										CAST(COUNT(*) AS BIT)
									FROM
										INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
											INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU ON TC.TABLE_NAME = CCU.TABLE_NAME AND TC.CONSTRAINT_NAME = CCU.CONSTRAINT_NAME
									WHERE
										TC.TABLE_NAME = C.TABLE_NAME
										AND
										TC.CONSTRAINT_TYPE = 'PRIMARY KEY'
										AND
										CCU.COLUMN_NAME = C.COLUMN_NAME
								)
								[IS_PRIMARY_KEY]	
							FROM 
								INFORMATION_SCHEMA.COLUMNS C
							WHERE 
								TABLE_SCHEMA = '{0}' 
								AND
								TABLE_NAME = '{1}'
							ORDER BY
								C.ORDINAL_POSITION,
								C.COLUMN_NAME",
							table.Schema.Name,
							table.Name
						),
					connection);

			using (var reader = command.ExecuteReader())
			{
				while (reader.Read())
				{
					var column = new Column(table);
					column.Name = reader.GetString(reader.GetOrdinal("COLUMN_NAME"));
					column.DataType = reader.GetString(reader.GetOrdinal("DATA_TYPE"));
					
					if (!reader.IsDBNull(reader.GetOrdinal("CHARACTER_MAXIMUM_LENGTH")))
					{
						column.MaximumCharacterLength = reader.GetInt32(reader.GetOrdinal("CHARACTER_MAXIMUM_LENGTH"));
					}

					column.IsNullable = reader.GetBoolean(reader.GetOrdinal("IS_NULLABLE"));
					column.ClrDataType = this.GetClrDataType(column.DataType, column.IsNullable);

					if (reader.GetBoolean(reader.GetOrdinal("IS_PRIMARY_KEY")))
					{
						table.
							PrimaryKeys.
							Add(
								new PrimaryKey(table)
								{
									Name = column.Name,
									DataType = column.DataType,
									ClrDataType = column.ClrDataType,
									MaximumCharacterLength = column.MaximumCharacterLength
								}
							);
					}
					else
					{
						table.Columns.Add(column);
					}
				}
			}
		}

		private Type GetClrDataType(string dataType, bool isNullable)
		{
			switch (dataType.ToLower())
			{
				case "tinyint":
					return isNullable ? typeof(byte?) : typeof(byte);
				case "bit":
					return isNullable ? typeof(bool?) : typeof(bool);
				case "date":
				case "datetime":
				case "datetime2":
					return isNullable ? typeof(DateTime?) : typeof(DateTime);
				case "smalldatetime":
					return isNullable ? typeof(DateTime?) : typeof(DateTime);
				case "datetimeoffset":
					return isNullable ? typeof(DateTimeOffset?) : typeof(DateTimeOffset);
				case "money":
				case "smallmoney":
				case "decimal":
				case "numeric":
					return isNullable ? typeof(decimal?) : typeof(decimal);
				case "real":
					return isNullable ? typeof(float?) : typeof(float);
				case "float":
					return isNullable ? typeof(double?) : typeof(double);
				case "smallint":
					return isNullable ? typeof(short?) : typeof(short);
				case "int":
					return isNullable ? typeof(int?) : typeof(int);
				case "bigint":
					return isNullable ? typeof(long?) : typeof(long);
				case "uniqueidentifier":
					return isNullable ? typeof(Guid?) : typeof(Guid);
				case "char":
				case "varchar":
				case "text":
				case "nchar":
				case "nvarchar":
				case "ntext":
				case "xml":
					return typeof(string);
				case "time":
					return isNullable ? typeof(TimeSpan) : typeof(TimeSpan);
				case "image":
				case "binary":
				case "varbinary":
				case "rowversion":
				case "timestamp":
					return typeof(byte[]);
			}

			return typeof(object);
		}
	}
}
